# Python & Pandas 调用 Oracle 操作例

import cx_Oracle
import pandas as pd

conn = cx_Oracle.connect('USER', 'PASSWORD',
                         'IPADDR/DATABASE', encoding='UTF-8')
data_sql = '''
SELECT DBMS_UTILITY.GET_HASH_VALUE(ACCT_NO, 0, POWER(2, 20)) ACCT_NO,
       SUBSTR(TRAN_DATE, 1, 4) TRAN_DATE,
       DBMS_UTILITY.GET_HASH_VALUE(DFZH, 0, POWER(2, 20)) DFZH,
       DBMS_UTILITY.GET_HASH_VALUE(PAY_USAGE_T, 0, POWER(2, 20)) PAY_USAGE_T
FROM FDTS.YD_TRAN_LV2
'''

df = pd.read_sql_query(data_sql, conn)

group_view = df.groupby(['ACCT_NO', 'TRAN_DATE'])
group_counts = group_view.apply(
    lambda _df: _df.groupby(['DFZH', 'PAY_USAGE_T']).ngroups
).reset_index(name='COUNT').sort_values(['ACCT_NO', 'TRAN_DATE']).reset_index(drop=True)

data_ret_sql = '''
SELECT ACCT_NO, TRAN_DATE, COUNT(*) "COUNT" FROM (
SELECT DISTINCT DBMS_UTILITY.GET_HASH_VALUE(ACCT_NO, 0, POWER(2, 20)) ACCT_NO,
       SUBSTR(TRAN_DATE, 1, 4) TRAN_DATE,
       DBMS_UTILITY.GET_HASH_VALUE(DFZH, 0, POWER(2, 20)) DFZH,
       DBMS_UTILITY.GET_HASH_VALUE(PAY_USAGE_T, 0, POWER(2, 20)) PAY_USAGE_T
FROM FDTS.YD_TRAN_LV2
)
GROUP BY ACCT_NO, TRAN_DATE
ORDER BY ACCT_NO, TRAN_DATE
'''

df_sql = pd.read_sql_query(data_ret_sql, conn)

print(group_counts.equals(df_sql))

conn.close()
